package ar.com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.configuration.CompositeConfiguration;
import org.apache.commons.configuration.PropertiesConfiguration;
import org.apache.commons.configuration.SystemConfiguration;

//import org.apache.commons.configuration.CompositeConfiguration;
//import org.apache.commons.configuration.PropertiesConfiguration;
//import org.apache.commons.configuration.SystemConfiguration;

import ar.com.dto.DeptoDTO;

public class DeptoDAOImpl implements DeptoDAO {

	@Override
	public List<DeptoDTO> buscar(int deptno) {

		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DeptoDTO>dto = new ArrayList<DeptoDTO>();	
			

		try {
			CompositeConfiguration config = new CompositeConfiguration();
			config.addConfiguration(new SystemConfiguration());
			config.addConfiguration(new
			PropertiesConfiguration("jdbc.properties"));

			String usuario = config.getString("usuario");
			String pass = config.getString("pass");
			String driver = config.getString("driver");
			String url = config.getString("url");

			Class.forName(driver);
			Connection con = DriverManager.getConnection(url, usuario, pass);

			String sql = "Select deptno, dname, loc ";
			sql += "FROM departamentos ";
			sql += "WHERE deptno =?";

			ps = con.prepareStatement(sql);
			ps.setInt(1, deptno);

			rs = ps.executeQuery();

			while (rs.next()) 
			{
				DeptoDTO dDto = new DeptoDTO();
				dDto.setDeptno(rs.getInt("deptno"));
				dDto.setDname(rs.getString("dname"));
				dDto.setLoc(rs.getString("loc"));
				dto.add(dDto);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return  dto;
	}

	@Override
	public Collection buscarPorLoc(Connection con, String loc) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<DeptoDTO> buscarAll() {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<DeptoDTO>dto = new ArrayList<DeptoDTO>();	
			

		try {
		
			CompositeConfiguration config = new CompositeConfiguration();
			config.addConfiguration(new SystemConfiguration());
			config.addConfiguration(new
			PropertiesConfiguration("jdbc.properties"));

			String usuario = config.getString("usuario");
			String pass = config.getString("pass");
			String driver = config.getString("driver");
			String url = config.getString("url");

			Class.forName(driver);
			Connection con = DriverManager.getConnection(url, usuario, pass);

			String sql = "Select deptno, dname, loc FROM departamentos";
		
			ps = con.prepareStatement(sql);			

			rs = ps.executeQuery();

			while (rs.next()) 
			{
				DeptoDTO dDto = new DeptoDTO();
				dDto.setDeptno(rs.getInt("deptno"));
				dDto.setDname(rs.getString("dname"));
				dDto.setLoc(rs.getString("loc"));
				dto.add(dDto);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return  dto;
		
	}

}
